package org.compiere.process;

import java.io.File;
import java.io.FileOutputStream;
import java.math.BigDecimal;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.logging.Level;
import jxl.format.Alignment;
import jxl.format.Border;
import jxl.format.BorderLineStyle;
import jxl.format.Colour;
import jxl.format.VerticalAlignment;
import jxl.write.DateFormat;
import jxl.write.WritableCellFormat;
import net.sf.jasperreports.engine.JRException;
import org.adempiere.exceptions.AdempiereException;
import org.apache.commons.io.filefilter.WildcardFileFilter;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Row;
import org.compiere.dsr.DSR_ExcelStyles;
import org.compiere.model.I_BPM_Form;
import org.compiere.model.I_C_Period;
import org.compiere.model.MAttachmentEntry;
import org.compiere.model.MBPMForm;
import org.compiere.model.MBPMFormCode;
import org.compiere.model.MBPMFormColumn;
import org.compiere.model.MBPMFormLine;
import org.compiere.model.MParameter;
import org.compiere.model.MPeriod;
import org.compiere.model.MTable;
import org.compiere.util.DB;
import org.compiere.util.Msg;
import extend.org.compiere.utils.Util;


public class BPM_CashExecution extends SvrProcess {
	
	MPeriod period;
	int daysAmount;
	int AD_Table_ID;
	// Style for value column
	static	WritableCellFormat nn = new WritableCellFormat();
	static WritableCellFormat valuestyle = new WritableCellFormat();
	static WritableCellFormat namestyle = new WritableCellFormat();
	static WritableCellFormat numcolstyle = new WritableCellFormat();
	static WritableCellFormat lastNumcolstyle = new WritableCellFormat();
	static WritableCellFormat totalrowstyle = new WritableCellFormat();
	static DateFormat customDateFormat = new DateFormat ("dd.MM.yy");
    static WritableCellFormat dateFormat = new WritableCellFormat (customDateFormat);
    static WritableCellFormat totalDateFormat = new WritableCellFormat (customDateFormat);
    
	static CellStyle headerStyle = null;
	static CellStyle commonStyle = null;
	static CellStyle rowHeader = null;
    
    static {
    		try {
				nn.setBorder(Border.LEFT, BorderLineStyle.THIN, Colour.BLACK);
				nn.setBorder(Border.RIGHT, BorderLineStyle.THIN, Colour.BLACK);
				nn.setBorder(Border.BOTTOM, BorderLineStyle.THIN, Colour.BLACK);
				nn.setBorder(Border.TOP, BorderLineStyle.THIN, Colour.BLACK);
				nn.setAlignment(Alignment.CENTRE);
				nn.setVerticalAlignment(VerticalAlignment.CENTRE);
				nn.setWrap(true);				
			    
			    valuestyle.setBorder(Border.LEFT, BorderLineStyle.THIN, Colour.BLACK);
			    valuestyle.setBorder(Border.RIGHT, BorderLineStyle.THIN, Colour.BLACK);
			    valuestyle.setBorder(Border.BOTTOM, BorderLineStyle.THIN, Colour.BLACK);
			    valuestyle.setBorder(Border.TOP, BorderLineStyle.THIN, Colour.BLACK);
			    valuestyle.setAlignment(Alignment.LEFT);
			    valuestyle.setVerticalAlignment(VerticalAlignment.CENTRE);
			    valuestyle.setBackground(Colour.GREY_25_PERCENT);
			    valuestyle.setWrap(true);
			    valuestyle.setShrinkToFit(true);
			    // Style for name column
			    
			    namestyle.setBorder(Border.ALL, BorderLineStyle.THIN, Colour.BLACK);
			    namestyle.setAlignment(Alignment.LEFT);
			    namestyle.setVerticalAlignment(VerticalAlignment.CENTRE);
			    namestyle.setWrap(true);
			    //Style for number column
			    
			    numcolstyle.setBorder(Border.ALL, BorderLineStyle.THIN, Colour.BLACK);
			    numcolstyle.setAlignment(Alignment.RIGHT);
			    numcolstyle.setVerticalAlignment(VerticalAlignment.CENTRE);
			    numcolstyle.setWrap(true);
			    //Style for last number column
			    
			    lastNumcolstyle.setBorder(Border.RIGHT, BorderLineStyle.THIN, Colour.BLACK);
			    lastNumcolstyle.setBorder(Border.LEFT, BorderLineStyle.THIN, Colour.BLACK);
			    lastNumcolstyle.setBorder(Border.TOP, BorderLineStyle.THIN, Colour.BLACK);
			    lastNumcolstyle.setBorder(Border.BOTTOM, BorderLineStyle.THIN, Colour.BLACK);
			    lastNumcolstyle.setAlignment(Alignment.RIGHT);
			    lastNumcolstyle.setVerticalAlignment(VerticalAlignment.CENTRE);
			    lastNumcolstyle.setWrap(true);
			    //Style for total row
			    
			    totalrowstyle.setBorder(Border.ALL, BorderLineStyle.THIN, Colour.BLACK);
			    totalrowstyle.setAlignment(Alignment.CENTRE);
			    totalrowstyle.setVerticalAlignment(VerticalAlignment.CENTRE);
			    totalrowstyle.setWrap(true);
			    totalrowstyle.setBackground(Colour.GREY_25_PERCENT);
			    totalrowstyle.setShrinkToFit(true);
			    
			    dateFormat.setBorder(Border.LEFT, BorderLineStyle.THIN, Colour.BLACK);
			    dateFormat.setBorder(Border.RIGHT, BorderLineStyle.THIN, Colour.BLACK);
			    dateFormat.setBorder(Border.TOP, BorderLineStyle.THIN, Colour.BLACK);
			    dateFormat.setBorder(Border.BOTTOM, BorderLineStyle.THIN, Colour.BLACK);
			    dateFormat.setAlignment(Alignment.RIGHT);
			    dateFormat.setVerticalAlignment(VerticalAlignment.CENTRE);			    
			    
			    totalDateFormat.setBorder(Border.LEFT, BorderLineStyle.THIN, Colour.BLACK);
			    totalDateFormat.setBorder(Border.RIGHT, BorderLineStyle.THIN, Colour.BLACK);
			    totalDateFormat.setBorder(Border.TOP, BorderLineStyle.THIN, Colour.BLACK);
			    totalDateFormat.setBorder(Border.BOTTOM, BorderLineStyle.THIN, Colour.BLACK);
			    totalDateFormat.setAlignment(Alignment.RIGHT);
			    totalDateFormat.setVerticalAlignment(VerticalAlignment.CENTRE);
			    totalDateFormat.setBackground(Colour.GREY_25_PERCENT);
    		}
    		catch(Exception ex){}
    }
    
	private int BPM_Form_ID;
	private int C_Period_ID;
	private String path = "";
	
	@Override
	protected void prepare() {
		ProcessInfoParameter[] para = getParameter();
		for (int i = 0; i < para.length; i++)	{
			String name = para[i].getParameterName();
			if (name == null); 
				//
			else if (name.compareTo(I_BPM_Form.COLUMNNAME_BPM_Form_ID) == 0 && para[i].getParameter() != null)
				BPM_Form_ID = new BigDecimal(Integer.parseInt(para[i].getParameter().toString())).intValue();
			else if (name.compareTo(I_C_Period.COLUMNNAME_C_Period_ID) == 0 && para[i].getParameter() != null)
				C_Period_ID = new BigDecimal(Integer.parseInt(para[i].getParameter().toString())).intValue();
			else if (name.equals("Path"))
				path = (String)para[i].getParameter();
			else
			{
				log.log(Level.INFO, "Unknown Parameter: " + name);
			}
		}
		
		if(path == null || path.isEmpty())
		{
			path = Util.localFilePath;
		}
		else
			path = path + System.getProperty("file.separator");
	}

	@Override
	protected String doIt() throws Exception {
		String patternName = "report";
		//get attachment FROM the process
		MAttachmentEntry entry = Util.getAttachment(getProcessInfo(), getCtx(), patternName);
		if(entry == null)
			throw new AdempiereException(Msg.translate(getCtx(), "NotFoundTemplate")+" "+patternName);
				
		HSSFWorkbook wb = new HSSFWorkbook(entry.getInputStream());
		HSSFSheet sheet = wb.getSheetAt(0);
		
		headerStyle = DSR_ExcelStyles.getHeaderStyle(wb);
		commonStyle = DSR_ExcelStyles.getCommonStyle(wb);
		rowHeader = DSR_ExcelStyles.getRowHeader(wb);
		
		MBPMFormLine[] lines = MBPMForm.getLines(getCtx(), BPM_Form_ID, get_TrxName());
		MBPMFormColumn[] columns = MBPMFormCode.getColumns(getCtx(), (new MBPMForm(getCtx(), BPM_Form_ID, get_TrxName())).getBPM_FormCode_ID(), get_TrxName());
		
		AD_Table_ID = MTable.getTable_ID("BPM_FormLine");
		period = new MPeriod(getCtx(), C_Period_ID, get_TrxName());
		daysAmount = 31; //period.getEndDate().getDate() - period.getStartDate().getDate() + 1;
		
		HSSFCell cell = Util.getCellStart(sheet, wb, ">>");
		
		int rowIndex = 0;
		int columnIndex = 0;
		
		if(cell == null) {			
			rowIndex = 0;		
			columnIndex = 1;
		}
		else {
			rowIndex = cell.getRowIndex();		
			columnIndex = cell.getColumnIndex();
		}
		
		//writeToExcel_ColumnHeader(rowIndex, columnIndex, sheet, columns);
		//rowIndex++;	
		
		for(MBPMFormLine line : lines) {
			writeToExcel_Amount(rowIndex++, columnIndex, wb, sheet, line, columns);
		}

		String fileOutPath = getXlsName(entry.getFile().getPath());		
		FileOutputStream fileOut = new FileOutputStream(fileOutPath);
		wb.write(fileOut);
		fileOut.close();
		
		Util.launchFile(new File(fileOutPath));
		return null;
	}

	private List<Integer> getCharge(int BPM_FormLine_ID, int AD_Table_ID) {
		
		List<Integer> charges = new ArrayList<Integer>();
		
		String sql = String.format("Select h.C_Charge_ID \n" +
					 "From adempiere.BPM_FormLine l \n" +  
					 "Inner Join adempiere.BPM_Form f On f.BPM_Form_ID = l.BPM_Form_ID \n" +
					 "Inner Join adempiere.BPM_FormCode c On c.BPM_FormCode_ID = f.BPM_FormCode_ID \n" +
					 "Inner Join adempiere.BPM_Parameters p On p.AD_Table_ID = %d And p.Record_ID = l.BPM_FormLine_ID \n" +
					 "Inner Join adempiere.C_Charge h On h.C_Charge_ID = p.value1::Integer \n " +
					 "Where l.BPM_FormLine_ID = %d", AD_Table_ID, BPM_FormLine_ID);
		
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		
		try {
			pstmt = DB.prepareStatement(sql, get_TrxName());
			rs = pstmt.executeQuery();
			
			while(rs.next()) {
				charges.add(rs.getInt(1));
			}
		}
		catch(Exception ex){}
		
		return charges;
	}
	
	/*private BigDecimal getAmount(MPeriod period, int day, List<Integer> charges) {
		
		System.out.println(String.format("Day = %d Period = %d charges = " + charges, day, period.get_ID()));
		
		Timestamp date1 = period.getStartDate();
		Timestamp date2 = period.getStartDate();

		date1.setDate(day);
		date2.setDate(day);		
		
		StringBuilder sql = new StringBuilder(String.format("Select COALESCE(Sum(Amount),0) From bpm_cashexecution_report Where DateAcct Between ? And ? And C_Charge_ID In(", date1, date2));
		
		for(Integer charge : charges) {
			sql.append(charge).append(",");
		}
		sql.replace(sql.length()-1, sql.length(), "");
		sql.append(")");
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		
		BigDecimal amount = new BigDecimal(0d);
		if(charges.size() == 0)
			return amount;
		try {
			pstmt = DB.prepareStatement(sql.toString(), get_TrxName());
			pstmt.setDate(1, new Date(date1.getTime()));
			pstmt.setDate(2, new Date(date2.getTime()));
			rs = pstmt.executeQuery();			
			
			if(rs.next())
				amount = rs.getBigDecimal(1);
		}
		catch(Exception ex){}
		finally {
			try {
				rs.close(); pstmt.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			rs = null; pstmt = null;
		}
		return amount;
	}*/
	
	private void writeToExcel_ColumnHeader(int rowIndex, int columnIndex, HSSFSheet sheet, MBPMFormColumn[] columns)
	{
		Row xlsRow = sheet.createRow(rowIndex);
		xlsRow.setHeight((short)600);
		Cell xlsCell = null;
		for(MBPMFormColumn column : columns) {
			if(daysAmount < columnIndex)
				break;			
			xlsCell = xlsRow.createCell(columnIndex);		
			xlsCell.setCellStyle(headerStyle);
			xlsCell.setCellValue(column.getName());
			columnIndex++;
		}
		
		xlsCell = xlsRow.createCell(columnIndex);		
		xlsCell.setCellStyle(headerStyle);
		xlsCell.setCellValue("ИТОГО:");
		columnIndex++;
	}
	
	private void writeToExcel_Amount(int rowIndex, int columnIndex, HSSFWorkbook wb, HSSFSheet sheet, MBPMFormLine line, MBPMFormColumn[] columns)
	{
		List<Integer> charges = getCharge(line.get_ID(), AD_Table_ID);
		
		Row xlsRow = sheet.createRow(rowIndex);
		int rowHeigth = 300;
		xlsRow.setHeight((short)rowHeigth);		
		CellStyle style = wb.createCellStyle();
		style.setAlignment(CellStyle.ALIGN_RIGHT);
		style.setVerticalAlignment(CellStyle.ALIGN_CENTER);
		HSSFFont font = wb.createFont();			
	    style.setBorderBottom((short)1);
		style.setBorderTop((short)1);
		style.setBorderLeft((short)1);
		style.setBorderRight((short)1);
		style.setWrapText(true);
		
		CellStyle rowStyle = wb.createCellStyle();
		rowStyle.setAlignment(CellStyle.ALIGN_LEFT);
		rowStyle.setVerticalAlignment(CellStyle.ALIGN_CENTER);
		HSSFFont rowFont = wb.createFont();			
		rowStyle.setBorderBottom((short)1);
		rowStyle.setBorderTop((short)1);
		rowStyle.setBorderLeft((short)1);
		rowStyle.setBorderRight((short)1);
		rowStyle.setWrapText(true);
		
		if(charges.size() > 1) {
			font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
			rowFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
		}
		style.setFont(font);
		rowStyle.setFont(rowFont);
		
		BigDecimal value = null;
		BigDecimal totalLine = new BigDecimal(0d);
		
		sheet.setColumnWidth(columnIndex - 1, 12000);
		Cell xlsCell = xlsRow.createCell(columnIndex - 1);
		xlsCell.setCellStyle(rowStyle);
		xlsCell.setCellValue(line.getName());
		
		Timestamp date1 = period.getStartDate();
		
		LinkedHashMap<String, Object> params = new LinkedHashMap<String, Object>();
		
		String chargeStr = "0";
		
		if(charges.size() > 0)
			for(Integer charge : charges) {
				chargeStr += ", " + charge;
			}
		
		params.put("C_Charge_ID", chargeStr);
		
		String sqlValue = "";
		int j = 0;
		for(int i = columnIndex; i < daysAmount + columnIndex; i++) {
			date1.setDate(i);
			params.put("DateAcct", date1);
			
			MParameter param = new MParameter(getCtx(), columns[j].getBSC_Parameter_ID(), get_TrxName());
			sqlValue = param.getZeroParameterLine().calculate(params);
			if(sqlValue == null || sqlValue.isEmpty())
				value = new BigDecimal(0d);
			else
				value = new BigDecimal(sqlValue);
			
			//value = getAmount(period, i, charges);				
			totalLine = totalLine.add(value);				
			xlsCell = xlsRow.createCell(i);
			xlsCell.setCellStyle(style);
			xlsCell.setCellValue(value.doubleValue());
			j++;
		}
		
		xlsCell = xlsRow.createCell(daysAmount + columnIndex);
		xlsCell.setCellStyle(style);
		xlsCell.setCellValue(totalLine.doubleValue());		
	}
	
	private static String getXlsName(String reportName) throws JRException {
		String Result = null;
		String tmpName = reportName;
		Integer i = 0;
		
		File d = new File(Util.localFilePath);
        String[] lF = d.list(new WildcardFileFilter(tmpName+"*.xls"));
        
        for(int j=0; j < lF.length; j++) {
        	File tmp = new File(lF[j]);
        	tmp.delete();
        }
        
		boolean flag = true;
		while (flag) {
			File f = new File(Util.localFilePath +"/"+ tmpName+".xls");
			try { 
				if (f.exists() && !f.delete()) {
					tmpName = reportName + "(" +(++i).toString() + ")";
				} else {
					flag = false;
				}
			} catch (Exception e) {
				e.printStackTrace();
				tmpName = reportName + "(" +(++i).toString() + ")";
			}
		}
		Result = Util.localFilePath +"/"+ tmpName+".xls";
		return Result;
	}
}
